import xlrd
import re
from typing import List, Dict, Any, Optional, Tuple
from django.core.files.uploadedfile import InMemoryUploadedFile
from .prefectures import PREFECTURE_NAMES
import io
from reportlab.pdfgen import canvas
from reportlab.lib.pagesizes import A4
from reportlab.lib.units import mm, inch
from datetime import datetime
from openpyxl.utils import get_column_letter, column_index_from_string
import os
from django.conf import settings
import requests
from lxml import html
from .models import LocalOrder, TransferOrder
import json
from openpyxl.styles import Alignment, Border, Side, PatternFill, Font
import openpyxl
import io
from io import BytesIO
from django.http import HttpResponse
import urllib.parse



# 智能标准化日本都道府县名称
def normalize_japanese_prefecture(city_value, state_value, address1=None, address2=None, attention=None, company_name=None):
    """
    智能标准化日本都道府县名称
    Args:
        city_value: 城市字段值
        state_value: 州/县字段值
        address1: 地址1字段值
        address2: 地址2字段值
        attention: 收件人字段值
        company_name: 公司名称字段值
    Returns:
        str: 标准化的都道府县名称，如果未找到则返回空字符串
    """
    def clean_text(text):
        if not text:
            return ""
        # 移除空格和全角空格
        text = text.strip().replace('　', ' ').replace(',', ' ').replace('、', ' ')
        # 统一英文到大写
        return re.sub(r'\s+', ' ', text).upper()

    def find_prefecture(text):
        if not text:
            return None
            
        # 清理文本
        text = clean_text(text)
        
        # 1. 检查是否已经是标准名称
        for prefecture in PREFECTURE_NAMES:
            if prefecture in text:
                return prefecture
                
        # 2. 移除英文后缀并检查
        text_without_suffix = re.sub(r'\s*(KEN|TO|FU|DO)$', '', text, flags=re.IGNORECASE)
        
        # 3. 尝试从PREFECTURE_NAMES中找到最匹配的名称
        candidates = []
        for prefecture in PREFECTURE_NAMES:
            # 去掉后缀进行比较
            prefecture_base = prefecture[:-1]  # 去掉最后的都、道、府、県
            
            # 如果文本中包含县名（无论是否有后缀）
            if prefecture_base in text or prefecture in text:
                return prefecture
                
            # 对于英文输入，尝试转换为片假名进行模糊匹配
            # 这里可以使用外部服务或库来进行更准确的转换
            # 暂时使用简单的字符串相似度匹配
            if text_without_suffix and (
                text_without_suffix in prefecture_base or
                prefecture_base in text_without_suffix
            ):
                candidates.append(prefecture)
        
        # 如果找到候选项，返回第一个
        if candidates:
            return candidates[0]
            
        # 4. 如果文本是纯汉字，尝试添加正确的后缀
        if re.match(r'[一-龯]+$', text):
            # 检查是否在标准名称列表中
            for prefecture in PREFECTURE_NAMES:
                if text in prefecture:
                    return prefecture
            
            # 如果不在标准列表中，但确实是汉字，添加県后缀
            # 这里可以根据需要调整策略
            test_name = text + '県'
            if test_name in PREFECTURE_NAMES:
                return test_name
        
        return None

    # 按优先级顺序查找都道府县名称
    search_fields = [
        state_value,      # 优先从state字段查找
        city_value,       # 其次从city字段查找
        address1,         # 然后从address1查找
        address2,         # 从address2查找
        company_name,     # 从公司名称查找
        attention         # 最后从收件人查找
    ]
    
    # 逐个字段搜索
    for field_value in search_fields:
        if field_value:
            # 如果字段包含多个部分，分割后逐个检查
            parts = re.split(r'[,、\s　]+', str(field_value))
            for part in parts:
                prefecture = find_prefecture(part)
                if prefecture:
                    return prefecture
    
    # 如果所有字段都没找到，返回默认值
    return ''


# 查询日本邮政编码对应的地址信息
def search_postal_code(postal_code):
    url = f'https://www.post.japanpost.jp/cgi-zip/zipcode.php?zip={postal_code}'
    headers = {
        'user-agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36'
    }
    response = requests.get(url, headers=headers)

    if response.status_code == 200:
        tree = html.fromstring(response.content)
        try:
            _1 = tree.xpath("//div[@class='pad']/table[@class='prefList sp-b10']/tbody/tr[2]/td[@class='data'][2]/small/text()")[0]
            _2 = tree.xpath("//div[@class='pad']/table[@class='prefList sp-b10']/tbody/tr[2]/td[@class='data'][3]/small/text()")[0]
            _3 = tree.xpath("//div[@class='pad']/table[@class='prefList sp-b10']/tbody/tr[2]/td[4]/div[@class='data']/p/small/a[@class='line']/text()")[0]
            _1 = _1 + _2 + _3
            return (_1)
        except Exception as e:
            _1 = _search_postal_code(postal_code)
            return (_1)
    else:
        print(f'请求失败，状态码: {response.status_code}')
        return None

def _search_postal_code(postal_code):
    url = f'https://www.navitime.co.jp/freeword/?keyword={postal_code}'
    headers = {
        'user-agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36'
    }
    response = requests.get(url, headers=headers)

    if response.status_code == 200:
        tree = html.fromstring(response.content)
        try:
            # 获取第一个地址链接的文本
            _1 = tree.xpath("//div[@id='address_area']//li[@class='address_link']/a/text()")[0]
            # 去掉"丁目"及前面的数字
            if _1 and '丁目' in _1:
                # 使用正则表达式去掉数字+丁目
                import re
                _1 = re.sub(r'\d+丁目', '', _1)
            return (_1)
        except Exception as e:
            print(f'错误，navitime.co.jp: {e}')
            return None
    else:
        print(f'请求失败，状态码: {response.status_code}')
        return None


# 批量查询邮政编码并更新订单地址
def batch_search_postal_codes(postal_code_list, model):
    """
    批量查询邮政编码并更新订单地址
    Args:
        postal_code_list: 包含postal_code和fs_no/cw_no的字典列表
        model: 订单模型类（LocalOrder或TransferOrder）
    Returns:
        Tuple[List, List]: 成功和失败的结果列表
    """
    results = []
    errors = []
    
    for item in postal_code_list:
        postal_code = item.get('postal_code', '').replace('-', '').replace('〒', '')
        fs_no = item.get('fs_no', '')
        cw_no = item.get('cw_no', '')
        
        if not postal_code:
            errors.append({
                'fs_no': fs_no,
                'cw_no': cw_no,
                'postal_code': postal_code,
                'error': '邮编不能为空'
            })
            continue
            
        # 查询邮编信息
        address_info = search_postal_code(postal_code)
        
        if not address_info:
            errors.append({'fs_no': fs_no,'cw_no': cw_no,'postal_code': postal_code,'error': '未找到该邮编对应的地址信息'})
            continue
        
        try:
            # 尝试查询订单对象
            order = None
            if model == LocalOrder and fs_no:
                order = model.objects.get(fs_no=fs_no)
            elif model == TransferOrder and cw_no:
                order = model.objects.get(cw_no=cw_no)
            
            if not order:
                errors.append({'fs_no': fs_no,'cw_no': cw_no,'postal_code': postal_code,'error': '未找到对应的订单'})
                continue
            
            # 更新订单的地址信息 - 适配新的邮编查询工具返回格式
            order.state = address_info
            order.save()
            
            # 根据模型类型返回不同的结果
            result = {
                'postal_code': postal_code,
                'address': address_info,
                'fs_no': fs_no,
                'cw_no': cw_no
            }
            
            # 添加模型特定的字段
            if model == LocalOrder:
                result['fs_no'] = order.fs_no
                result['cw_no'] = order.tracking_number or ''
            else:  # TransferOrder
                result['fs_no'] = order.fs_no or ''
                result['cw_no'] = order.cw_no
            
            results.append(result)
            
        except model.DoesNotExist:
            errors.append({
                'fs_no': fs_no,
                'cw_no': cw_no,
                'postal_code': postal_code,
                'error': '未找到对应的订单'
            })
        except Exception as e:
            errors.append({
                'fs_no': fs_no,
                'cw_no': cw_no,
                'postal_code': postal_code,
                'error': str(e)
            })
    
    return results, errors


# 检查上传的Excel文件
def check_excel_file(file):
    """
    检查上传的Excel文件
    
    Args:
        file: 上传的文件对象
        
    Returns:
        tuple: (is_valid, error_message)
            - is_valid: 布尔值，表示文件是否有效
            - error_message: 如果文件无效，返回错误信息；否则为None
    """
    # 检查是否上传了文件
    if not file:
        return False, '请上传文件'
        
    # 检查文件扩展名
    if not file.name.endswith('.xls'):
        return False, '请上传.xls格式的文件'
        
    return True, None


# 读取xls文件数据
def read_xls_data(file):
    """
    读取xls文件数据
    
    Args:
        file: 上传的Excel文件
        
    Returns:
        List[Dict[str, Any]]: 包含Excel数据的字典列表
    """ 
    try:
        # 读取Excel文件
        file.open()
        file_content = file.read()
        workbook = xlrd.open_workbook(file_contents=file_content, ignore_workbook_corruption=True)
        sheet = workbook.sheet_by_index(0)

        # 获取表头
        headers = [str(cell.value).strip() for cell in sheet.row(0)]
        
        data = []
        # 从第二行开始读取数据
        for row_idx in range(1, sheet.nrows):
            row = sheet.row_values(row_idx)
            row_data = {}
            
            for col_idx, header in enumerate(headers):
                value = row[col_idx]
                
                # 处理数字类型
                if isinstance(value, float) and value.is_integer():
                    value = int(value)
                    
                row_data[header] = value
                
            data.append(row_data)
        
        return data
    except Exception as e:
        raise ValueError(f'读取Excel文件失败: {str(e)}')


# 读取xls文件中指定单元格的内容
def read_xls_by_cell(file, cell_coordinate):
    try:
        # 检查文件格式
        if not file.name.endswith('.xls'):
            raise ValueError('文件格式不正确，请上传.xls格式的文件')
            
        # 检查单元格坐标格式
        if not re.match(r'^[A-Za-z]+[0-9]+$', cell_coordinate):
            raise ValueError('单元格坐标格式不正确，例如：A5, B10等')
            
        # 重置文件指针
        file.seek(0)

        file.open()
        file_content = file.read()
        # 尝试不同的方式打开工作簿
        try:
            # 方式1：直接使用文件内容
            workbook = xlrd.open_workbook(file_contents=file_content, ignore_workbook_corruption=True)
        except xlrd.XLRDError:
            # 方式2：使用formatting_info=False
            file.seek(0)  # 再次重置文件指针
            workbook = xlrd.open_workbook(file_contents=file_content, formatting_info=False, ignore_workbook_corruption=True)
            
        # 获取第一个工作表
        sheet = workbook.sheet_by_index(0)
        
        # 将单元格坐标转换为行列索引
        col = ord(cell_coordinate[0].upper()) - ord('A')
        row = int(cell_coordinate[1:]) - 1
        
        # 检查索引是否越界
        if row >= sheet.nrows or col >= sheet.ncols:
            return ''
            
        # 获取单元格内容
        cell_value = sheet.cell_value(row, col)
        
        # 如果单元格内容为空，返回空字符串
        if cell_value == '' or cell_value is None:
            return ''
            
        # 将内容转换为字符串并去除首尾空格
        return str(cell_value).strip()
            
    except xlrd.XLRDError as e:
        raise ValueError(f'Excel文件格式错误或已损坏: {str(e)}')
    except Exception as e:
        raise ValueError(f'读取单元格 {cell_coordinate} 失败: {str(e)}')


# 生成转运订单收据PDF
def generate_transfer_receipt_pdf(orders_data: List[Dict], carrier: str, username: str) -> bytes:
    # 创建PDF文件的内存缓冲区
    buffer = io.BytesIO()
    
    # 创建PDF对象
    p = canvas.Canvas(buffer, pagesize=A4)
    
    # 设置页面参数
    page_width, page_height = A4  # A4尺寸
    margin_top = 30 * mm  # 上边距
    margin_bottom = 30 * mm  # 下边距
    header_height = 35 * mm  # 表头高度
    row_base_height = 8 * mm  # 基础行高
    available_height = page_height - margin_top - margin_bottom  # 可用高度
    
    # 设置表格参数
    table_width = 510
    left_margin = 40
    headers = ['NO', 'Carrier', 'ZYJP', 'CW/HF', 'Boxes', 'Total']
    col_widths = [40, 60, 150, 160, 50, 50]
    
    # 计算每个订单的实际高度
    order_heights = []
    for order in orders_data:
        cw_count = len(order['cwNo']) if order['cwNo'] else 1
        height = max(row_base_height * cw_count, row_base_height)  # 确保最小高度
        order_heights.append(height)
    
    # 分页处理
    current_page = 0
    current_height = 0
    page_start_idx = 0
    
    while page_start_idx < len(orders_data):
        if current_page > 0:
            p.showPage()  # 创建新页面
        
        # 重置页面设置
        p.setLineWidth(0.5)
        current_height = page_height - margin_top
        
        # 添加页眉
        _add_page_header(p, left_margin, username)
        current_height -= header_height - 10 * mm  # 让表格向上移动10mm，更靠近页眉
        
        # 计算x坐标
        x_positions = _calculate_x_positions(left_margin, col_widths)
        
        # 绘制表头
        current_height = _draw_table_header(p, headers, x_positions, col_widths, 
                                         current_height, header_height/4, table_width, left_margin)
        
        # 确定当前页可以容纳多少订单
        page_orders = []
        page_heights = []
        temp_height = 0
        i = page_start_idx
        
        while i < len(orders_data):
            if temp_height + order_heights[i] <= (current_height - margin_bottom):
                page_orders.append(orders_data[i])
                page_heights.append(order_heights[i])
                temp_height += order_heights[i]
                i += 1
            else:
                break
        
        # 绘制当前页的内容
        _draw_table_content(p, page_orders, carrier, x_positions, col_widths, 
                          current_height, page_heights, table_width, left_margin)
        
        # 如果是最后一页，添加合计行
        if i >= len(orders_data):
            total_boxes = sum(safe_int(order['boxCount']) for order in orders_data)
            _draw_total_row(p, orders_data, total_boxes, x_positions, col_widths,
                          current_height - sum(page_heights), row_base_height, 
                          table_width, left_margin)
        
        # 添加页码
        p.setFont('Helvetica', 8)
        page_number_text = f'Page {current_page + 1}'
        p.drawString(page_width - 50, 30, page_number_text)
        
        # 更新索引和页码
        page_start_idx = i
        current_page += 1
    
    p.save()
    pdf_content = buffer.getvalue()
    buffer.close()
    return pdf_content


# 添加页面头部内容
def _add_page_header(canvas_obj, left_margin: int, username: str):
    logo_path = os.path.join(settings.BASE_DIR, 'static', 'images', 'logo.png')
    if os.path.exists(logo_path):
        canvas_obj.drawImage(logo_path, left_margin, 770, width=80, height=35, mask='auto')
    
    canvas_obj.setFont('Helvetica-Bold', 18)
    canvas_obj.drawString(160, 780, 'FS JAPAN - Transfer Receipt')
    
    canvas_obj.setFont('Helvetica', 10)
    canvas_obj.drawString(450, 780, f'Date: {datetime.now().strftime("%Y/%m/%d")}')
    canvas_obj.drawString(450, 765, f'Receiver: {username}')


# 计算每列的x坐标
def _calculate_x_positions(left_margin: int, col_widths: List[int]) -> List[int]:
    x_positions = []
    current_x = left_margin
    for width in col_widths:
        x_positions.append(current_x)
        current_x += width
    return x_positions


# 绘制表格头部
def _draw_table_header(canvas_obj, headers, x_positions, 
                      col_widths, y_position, header_height, 
                      table_width, left_margin):
    # 设置线条宽度
    canvas_obj.setLineWidth(0.5)
    
    # 绘制表头背景
    canvas_obj.setFillColorRGB(0.85, 0.85, 0.85)  # 稍微调暗背景色
    canvas_obj.rect(left_margin, y_position - header_height, table_width, header_height, fill=1, stroke=0)
    
    # 绘制表头文字
    canvas_obj.setFillColorRGB(0, 0, 0)
    canvas_obj.setFont('Helvetica-Bold', 11)  # 增大字号
    for i, header in enumerate(headers):
        header_width = canvas_obj.stringWidth(header, 'Helvetica-Bold', 11)
        x_center = x_positions[i] + (col_widths[i] - header_width) / 2
        # 调整文字垂直位置，使其在单元格中更居中
        canvas_obj.drawString(x_center, y_position - (header_height * 0.6), header)
    
    # 绘制表头框
    canvas_obj.rect(left_margin, y_position - header_height, table_width, header_height)
    
    return y_position - header_height


# 绘制表格内容
def _draw_table_content(canvas_obj, data: List[Dict], carrier: str, x_positions: List[int], 
                       col_widths: List[int], y_position: int, row_heights: List[int], 
                       table_width: int, left_margin: int):
    if not data:  # 如果没有数据，直接返回
        return
        
    total_height = sum(row_heights)
    
    # 绘制表格外框和竖线
    canvas_obj.rect(left_margin, y_position - total_height, table_width, total_height)
    for x in x_positions[1:]:
        canvas_obj.line(x, y_position, x, y_position - total_height)
    
    # 绘制数据
    current_y = y_position
    canvas_obj.setFont('Helvetica', 10)
    
    for idx, (order, row_height) in enumerate(zip(data, row_heights), start=1):
        line_height = row_height / (len(order['cwNo']) if order['cwNo'] else 1)
        
        # 绘制各列数据
        _draw_centered_text(canvas_obj, str(idx), x_positions[0], col_widths[0], current_y - line_height/2)
        _draw_centered_text(canvas_obj, carrier, x_positions[1], col_widths[1], current_y - line_height/2)
        _draw_centered_text(canvas_obj, order['zyzpNo'], x_positions[2], col_widths[2], current_y - line_height/2)
        
        # 绘制CW号（每行一个）- 检测并标记重复的CW号
        if order['cwNo']:
            # 统计CW号出现次数，识别重复项
            cw_counts = {}
            for cw in order['cwNo']:
                cw_counts[cw] = cw_counts.get(cw, 0) + 1
            
            for i, cw_number in enumerate(order['cwNo']):
                # 如果CW号重复出现，使用红色绘制
                if cw_counts[cw_number] > 1:
                    _draw_centered_text_with_color(canvas_obj, cw_number, x_positions[3], col_widths[3], 
                                          current_y - (i + 0.5) * line_height, color=(1, 0, 0))  # 红色
                else:
                    _draw_centered_text(canvas_obj, cw_number, x_positions[3], col_widths[3], 
                                      current_y - (i + 0.5) * line_height)
        
        # 绘制数量和Total
        box_count = str(safe_int(order['boxCount']))
        cw_count = str(len(order['cwNo']) if order['cwNo'] else 0)
        _draw_centered_text(canvas_obj, box_count, x_positions[4], col_widths[4], current_y - line_height/2)
        _draw_centered_text(canvas_obj, cw_count, x_positions[5], col_widths[5], current_y - line_height/2)
        
        # 绘制横线
        if idx < len(data):
            canvas_obj.line(left_margin, current_y - row_height, 
                          left_margin + table_width, current_y - row_height)
        
        current_y -= row_height


# 绘制合计行
def _draw_total_row(canvas_obj, data: List[Dict], total_boxes: int, x_positions: List[int], col_widths: List[int], 
                    y_position: int, row_height: int, table_width: int, left_margin: int):
    # 绘制背景
    canvas_obj.setFillColorRGB(0.9, 0.9, 0.9)
    canvas_obj.rect(left_margin, y_position - row_height, table_width, row_height, fill=1, stroke=1)
    
    # 绘制文字
    canvas_obj.setFillColorRGB(0, 0, 0)
    canvas_obj.setFont('Helvetica-Bold', 10)
    total_y = y_position - row_height/2
    
    _draw_centered_text(canvas_obj, 'Total', x_positions[0], col_widths[0], total_y)
    total_boxes_str = str(total_boxes)
    _draw_centered_text(canvas_obj, total_boxes_str, x_positions[4], col_widths[4], total_y)
    
    # 计算所有CW号的总数
    total_cw_count = sum(len(order['cwNo']) if order['cwNo'] else 0 for order in data)
    _draw_centered_text(canvas_obj, str(total_cw_count), x_positions[5], col_widths[5], total_y)


# 在指定位置绘制居中文本
def _draw_centered_text(canvas_obj, text: str, x_pos: int, width: int, y_pos: int):
    text_width = canvas_obj.stringWidth(text, canvas_obj._fontname, canvas_obj._fontsize)
    x_center = x_pos + (width - text_width) / 2
    canvas_obj.drawString(x_center, y_pos, text)


# 在指定位置绘制带颜色的居中文本
def _draw_centered_text_with_color(canvas_obj, text: str, x_pos: int, width: int, y_pos: int, color=(0, 0, 0)):
    # 设置颜色
    canvas_obj.setFillColorRGB(color[0], color[1], color[2])
    
    # 绘制文本
    text_width = canvas_obj.stringWidth(text, canvas_obj._fontname, canvas_obj._fontsize)
    x_center = x_pos + (width - text_width) / 2
    canvas_obj.drawString(x_center, y_pos, text)
    
    # 恢复默认黑色
    canvas_obj.setFillColorRGB(0, 0, 0)


# 计算订单的运费
def calculate_shipping_fee(order_list: List[str], model) -> Tuple[List[Dict], List[Dict]]:
    results = []
    errors = []
    
    # 从数据库获取运费配置
    from .models import ShippingFeeConfig
    
    try:
        # 获取启用的运费配置
        fee_configs = ShippingFeeConfig.objects.filter(is_active=True)
        
        # 按物流公司和体积分组
        sagawa_prices = {}
        yamato_prices = {}
        
        for config in fee_configs:
            if config.carrier == 'SAGAWA':
                if config.volume not in sagawa_prices:
                    sagawa_prices[config.volume] = {}
                sagawa_prices[config.volume][config.region] = config.price
            elif config.carrier == 'YAMATO':
                if config.volume not in yamato_prices:
                    yamato_prices[config.volume] = {}
                yamato_prices[config.volume][config.region] = config.price
                
    except Exception as e:
        raise Exception(f'读取运费配置失败: {str(e)}')

    # 定义都道府县列表
    prefectures = [
        '北海道', '青森県', '岩手県', '宮城県', '秋田県', '山形県', '福島県',
        '茨城県', '栃木県', '群馬県', '埼玉県', '千葉県', '東京都', '神奈川県',
        '新潟県', '富山県', '石川県', '福井県', '山梨県', '長野県', '岐阜県',
        '静岡県', '愛知県', '三重県', '滋賀県', '京都府', '大阪府', '兵庫県',
        '奈良県', '和歌山県', '鳥取県', '島根県', '岡山県', '広島県', '山口県',
        '徳島県', '香川県', '愛媛県', '高知県', '福岡県', '佐賀県', '長崎県',
        '熊本県', '大分県', '宮崎県', '鹿児島県', '沖縄県'
    ]

    def find_closest_volume(volume, price_dict):
        if not price_dict:
            return None
        volumes = list(price_dict.keys())
        if not volumes:
            return None
        # 找到大于等于当前体积的最小区间
        valid_volumes = [v for v in volumes if v >= volume]
        if valid_volumes:
            return min(valid_volumes)
        # 如果没有大于等于当前体积的区间，返回最大区间
        return max(volumes)

    def get_price(price_dict, target_volume, prefecture):
        if target_volume not in price_dict:
            return float('inf')
        return price_dict[target_volume].get(prefecture, float('inf'))

    for order_no in order_list:
        try:
            # 根据模型类型选择查询条件
            if model == LocalOrder:
                order = model.objects.get(fs_no=order_no)
                result_key = 'fs_no'
            else:  # TransferOrder
                order = model.objects.get(cw_no=order_no)
                result_key = 'cw_no'
            
            # 计算体积
            volume = order.calculate_volume()
            # 从state中提取都道府县
            state = order.state
            prefecture = None
            
            # 从state中提取都道府县
            for pref in prefectures:
                if pref in state:
                    prefecture = pref
                    break
            
            if not prefecture:
                errors.append({
                    result_key: order_no,
                    'error': '无法从地址中识别都道府县'
                })
                continue

            # 获取两种运输方式的价格
            sagawa_volume = find_closest_volume(volume, sagawa_prices)
            yamato_volume = find_closest_volume(volume, yamato_prices)

            if not sagawa_volume or not yamato_volume:
                errors.append({
                    result_key: order_no,
                    'error': '无法找到合适的体积区间'
                })
                continue

            sagawa_price = get_price(sagawa_prices, sagawa_volume, prefecture)
            yamato_price = get_price(yamato_prices, yamato_volume, prefecture)

            if sagawa_price == float('inf') and yamato_price == float('inf'):
                errors.append({
                    result_key: order_no,
                    'error': '无法找到对应的运费价格'
                })
                continue

            # 比较价格并选择最便宜的运输方式
            if yamato_price <= sagawa_price:
                shipping_method = 'YAMATO'
                price = yamato_price
            else:
                shipping_method = 'SAGAWA'
                price = sagawa_price

            # 更新订单的运输方式
            order.shipping_method = shipping_method
            order.sagawa_price = str(sagawa_price)
            order.yamato_price = str(yamato_price)
            order.save()

            result = {
                result_key: order_no,
                'volume': volume,
                'prefecture': prefecture,
                'shipping_method': '黑猫' if shipping_method == 'YAMATO' else '佐川',
                'sagawa_price': sagawa_price,
                'yamato_price': yamato_price
            }
            
            # 如果是转运单，添加fs_no
            if model == TransferOrder and order.fs_no:
                result['fs_no'] = order.fs_no

            results.append(result)

        except model.DoesNotExist:
            errors.append({
                result_key: order_no,
                'error': '未找到该订单'
            })
        except Exception as e:
            errors.append({
                result_key: order_no,
                'error': str(e)
            })

    return results, errors


# 将 (R, G, B) 转换为十六进制颜色代码
def rgb_to_hex(rgb):
    return '{:02X}{:02X}{:02X}'.format(*rgb)


# 设置Excel中指定单元格的宽度和高度
def _set_size_excel(sheet, cell_coordinate, width=None, height=None):
    try:
        # 获取行号和列号
        row = int(''.join(filter(str.isdigit, cell_coordinate)))
        col = ''.join(filter(str.isalpha, cell_coordinate)).upper()
        
        # 设置行高
        if height is not None:
            sheet.row_dimensions[row].height = height
        
        # 设置列宽
        if width is not None:
            sheet.column_dimensions[col].width = width
        
        return sheet
    except Exception as e:
        raise ValueError(f'设置单元格 {cell_coordinate} 尺寸失败: {str(e)}')


# 设置Excel中指定单元格的字体大小
def _set_fontsize_excel(sheet, cell_coordinate, font_size, is_bold=True, has_border=True):
    try:
        # 获取单元格
        cell = sheet[cell_coordinate]
        
        # 获取当前字体样式
        current_font = cell.font
        
        # 创建新的字体样式
        new_font = Font(
            name=current_font.name,
            size=font_size,
            bold=is_bold,  # 使用传入的粗体参数
            italic=current_font.italic,
            vertAlign=current_font.vertAlign,
            underline=current_font.underline,
            strike=current_font.strike,
            color=current_font.color
        )
        
        # 应用新的字体样式
        cell.font = new_font
        
        # 设置边框
        if has_border:
            thin_border = Border(
                left=Side(style='thin'),
                right=Side(style='thin'),
                top=Side(style='thin'),
                bottom=Side(style='thin')
            )
            cell.border = thin_border
        
        return sheet
    except Exception as e:
        raise ValueError(f'设置单元格 {cell_coordinate} 样式失败: {str(e)}')
    

# 设置Excel中指定单元格的填充颜色
def _set_fillcolor_excel(sheet, cell_coordinate, fill_color):
    try:
        # 获取单元格
        cell = sheet[cell_coordinate]
        
        # 设置填充颜色
        cell.fill = PatternFill(start_color=fill_color, end_color=fill_color, fill_type='solid')    
        
        return sheet
    except Exception as e:
        raise ValueError(f'设置单元格 {cell_coordinate} 填充颜色失败: {str(e)}')    


# 合并Excel中的单元格
def _merge_cells_excel(sheet, cell_range):
    try:
        # 合并单元格
        sheet.merge_cells(cell_range)
        
        return sheet
    except Exception as e:
        raise ValueError(f'合并单元格 {cell_range} 失败: {str(e)}')


# 设置Excel中指定单元格的对齐方式
def _set_alignment_excel(sheet, cell_coordinate, horizontal='center', vertical='center'):
    try:
        # 获取单元格
        cell = sheet[cell_coordinate]
        
        # 设置对齐方式
        cell.alignment = Alignment(
            horizontal=horizontal,
            vertical=vertical,
            wrap_text=True  # 自动换行
        )
        
        return sheet
    except Exception as e:
        raise ValueError(f'设置单元格 {cell_coordinate} 对齐方式失败: {str(e)}')


# 在Excel中指定单元格插入数据
def insert_excel_by_cell(worksheet, cell_coordinate, data, merge_range=None, font_size=None, is_bold=True, has_border=True, width=None, height=None, fill_color=None, horizontal='center', vertical='center'):
    try:
        # 检查目标单元格是否已经被合并
        target_cell = worksheet[cell_coordinate]
        if hasattr(target_cell, 'value') and hasattr(target_cell.value, '__class__') and 'MergedCell' in str(type(target_cell.value)):
            # 如果目标单元格是合并单元格的一部分，跳过设置值
            return worksheet
        
        # 如果指定了合并范围，先合并单元格
        if merge_range is not None:
            # 解析合并范围
            start_cell, end_cell = merge_range.split(':')
            # 获取起始和结束的行列号
            start_col = ''.join(filter(str.isalpha, start_cell))
            start_row = int(''.join(filter(str.isdigit, start_cell)))
            end_col = ''.join(filter(str.isalpha, end_cell))
            end_row = int(''.join(filter(str.isdigit, end_cell)))
            
            # 检查合并范围内是否有单元格已经被合并
            can_merge = True
            start_col_idx = column_index_from_string(start_col)
            end_col_idx = column_index_from_string(end_col)
            for row in range(start_row, end_row + 1):
                for col in range(start_col_idx, end_col_idx + 1):
                    col_letter = get_column_letter(col)
                    cell = worksheet[f"{col_letter}{row}"]
                    if hasattr(cell, 'value') and hasattr(cell.value, '__class__') and 'MergedCell' in str(type(cell.value)):
                        can_merge = False
                        break
                if not can_merge:
                    break
            
            if can_merge:
                # 合并单元格
                worksheet = _merge_cells_excel(worksheet, merge_range)
                
                # 为合并范围内的所有单元格设置边框
                if has_border:
                    thin_border = Border(
                        left=Side(style='thin'),
                        right=Side(style='thin'),
                        top=Side(style='thin'),
                        bottom=Side(style='thin')
                    )
                    for row in range(start_row, end_row + 1):
                        for col in range(start_col_idx, end_col_idx + 1):
                            col_letter = get_column_letter(col)
                            cell = worksheet[f"{col_letter}{row}"]
                            cell.border = thin_border
                
                # 为合并范围内的所有单元格设置宽高
                if width is not None or height is not None:
                    for row in range(start_row, end_row + 1):
                        for col in range(start_col_idx, end_col_idx + 1):
                            col_letter = get_column_letter(col)
                            worksheet = _set_size_excel(worksheet, f"{col_letter}{row}", width, height)
                
                # 获取合并区域的第一个单元格
                cell_coordinate = start_cell
            else:
                # 如果不能合并，只设置第一个单元格
                cell_coordinate = start_cell
        
        # 设置单元格值
        try:
            worksheet[cell_coordinate] = data
        except Exception as e:
            # 如果设置值失败，可能是因为单元格已经被合并，跳过
            if 'MergedCell' in str(e) or 'read-only' in str(e):
                return worksheet
            else:
                raise e
        
        # 设置单元格尺寸（如果不是合并单元格）
        if (width is not None or height is not None) and merge_range is None:
            worksheet = _set_size_excel(worksheet, cell_coordinate, width, height)
        
        # 设置字体
        if font_size is not None:
            worksheet = _set_fontsize_excel(worksheet, cell_coordinate, font_size, is_bold, has_border)  # 保持边框设置
        
        # 设置填充颜色
        if fill_color is not None:
            worksheet = _set_fillcolor_excel(worksheet, cell_coordinate, fill_color)
        
        # 设置对齐方式
        worksheet = _set_alignment_excel(worksheet, cell_coordinate, horizontal, vertical)
        
        # 为非合并单元格设置边框
        if has_border and merge_range is None:
            thin_border = Border(
                left=Side(style='thin'),
                right=Side(style='thin'),
                top=Side(style='thin'),
                bottom=Side(style='thin')
            )
            worksheet[cell_coordinate].border = thin_border
        
        return worksheet
    except Exception as e:
        raise ValueError(f'在单元格 {cell_coordinate} 插入数据失败: {str(e)}')


# 保存Excel文件
def save_excel(workbook, filename):
    try:
        # 创建内存中的Excel文件
        output = io.BytesIO()
        workbook.save(output)
        output.seek(0)
        
        # 创建响应
        response = HttpResponse(
            output.getvalue(),
            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
        response['Content-Disposition'] = f'attachment; filename={filename}'
        response['Access-Control-Expose-Headers'] = 'Content-Disposition'
        
        return response
    except Exception as e:
        raise ValueError(f'保存Excel文件失败: {str(e)}')


# 安全地将值转换为浮点数
def safe_float(value, default=0.0):
    if value is None or value == '':
        return default
    try:
        return float(value)
    except (ValueError, TypeError):
        return default

# 安全地将值转换为整数
def safe_int(value, default=0):
    if value is None or value == '':
        return default
    
    # 处理字符串形式的特殊值
    if isinstance(value, str):
        value_lower = value.lower()
        if value_lower in ['inf', 'infinity', 'nan']:
            return default
    
    try:
        float_val = float(value)
        # 检查是否为无穷大或NaN
        if float_val == float('inf') or float_val == float('-inf') or float_val != float_val:  # NaN检查
            return default
        return int(float_val)
    except (ValueError, TypeError):
        return default
    